import os
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows

class ListsExcel:
    def __init__(self):
        self.page_start = 1  # 导出开始页码
        self.page_end = 200  # 导出介绍页码
        self.file_name = ''  # 文件名称

    def create_excel(self, excel_fields, lists):
        title = list(excel_fields.values())

        data = []
        for row in lists:
            temp = []
            for key, excel_field in excel_fields.items():
                field_data = row[key]
                if isinstance(field_data, (int, float)) and len(str(field_data)) >= 12:
                    field_data = str(field_data) + "\t"
                temp.append(field_data)
            data.append(temp)

        wb = Workbook()
        ws = wb.active

        # 设置单元格内容
        for key, value in enumerate(title):
            # 单元格内容写入
            ws.cell(column=key + 1, row=1, value=value)

        row = 2  # 从第二行开始
        for item in data:
            column = 1
            for value in item:
                # 单元格内容写入
                ws.cell(column=column, row=row, value=value)
                column += 1
            row += 1

        # 设置标题样式
        title_scope = f"A1:{chr(ord('A') + len(title) - 1)}1"  # 第一（标题）范围（例：A1:D1)
        for cell in ws[title_scope]:
            cell.fill = PatternFill(fill_type="solid", fgColor="00B0F0")
            cell.font = Font(color="FFFFFF")

        # 设置边框
        all_cope = f"A1:{chr(ord('A') + len(title) - 1)}{row - 1}"  # 整个表格范围（例：A1:D5）
        for cell in ws[all_cope]:
            cell.border = Border(left=Side(border_style="thin"),
                                 right=Side(border_style="thin"),
                                 top=Side(border_style="thin"),
                                 bottom=Side(border_style="thin"))

        # 保存 Excel 文件
        export_cache = ExportCache()
        src = export_cache.get_src()

        if not os.path.exists(src):
            os.makedirs(src, mode=0o775)
        wb.save(f"{src}{self.file_name}")

        # 设置本地 Excel 缓存并返回下载地址
        vars = {"file": export_cache.set_file(self.file_name)}
        return f"(string)(url('api/download/export', {vars}, true, true))"